package com.b2c.repository.erp;

import com.b2c.entity.result.PagingResponse;
import com.b2c.entity.ErpContactAddressEntity;
import com.b2c.entity.ErpContactEntity;
import com.b2c.entity.erp.vo.ErpContactAddressVo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;

import java.util.ArrayList;
import java.util.List;

@Repository
public class ClientManageRepository {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    protected int getTotalSize() {
        return jdbcTemplate.queryForObject("SELECT FOUND_ROWS() as row_num;", int.class);
    }

    /**
     * 查询客户列表
     * @param pageIndex
     * @param pageSize
     * @param name
     * @param mobile
     * @return
     */
    public PagingResponse<ErpContactEntity> getClientList(Integer pageIndex, Integer pageSize, String name, String mobile,Integer type,Integer category){
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT SQL_CALC_FOUND_ROWS * ");
        sb.append(" FROM erp_contact where isDelete=0 ");

        List<Object> params = new ArrayList<>();
        if (!StringUtils.isEmpty(mobile)) {
            sb.append("AND linkMans = ? ");
            params.add(mobile);
        }
        if (!StringUtils.isEmpty(name)) {
            sb.append("AND name = ? ");
            params.add(name);
        }
        if (type != null) {
            sb.append("AND type = ? ");
            params.add(type);
        }

        if (!StringUtils.isEmpty(category)) {
            sb.append("AND cCategory = ? ");
            params.add(category);
        }

        sb.append(" ORDER BY id DESC LIMIT ?,?");

        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);

        List<ErpContactEntity> lists = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(ErpContactEntity.class), params.toArray(new Object[params.size()]));
        return new PagingResponse<>(pageIndex, pageSize, getTotalSize(), lists);
    }

    /**
     * 根据Id查询客户
     * @return
     */
    public ErpContactEntity getClientById(Integer id){
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT SQL_CALC_FOUND_ROWS * ");
        sb.append(" FROM erp_contact where isDelete=0 and id=? ");
        return jdbcTemplate.queryForObject(sb.toString(), new BeanPropertyRowMapper<>(ErpContactEntity.class),id );
    }

    /**
     * 新增客户
     * @param entity
     * @return
     */
   public Integer addManage(ErpContactEntity entity){
        return jdbcTemplate.update("INSERT INTO erp_contact SET name=?,number=?,cCategoryName=?,cCategory=?,contact=?,cLevelName=?,disable=?,type=?,isDelete=0,user_id=?,province=?,city=?,county=?,address=?",
                entity.getName(),entity.getNumber(),entity.getcCategoryName(),entity.getcCategory(),entity.getContact(),entity.getcLevelName()
                ,entity.getDisable(),entity.getType(),entity.getUserId()
        ,entity.getProvince(),entity.getCity(),entity.getCounty(),entity.getAddress());
   }

    /**
     * 修改客户
     * @param entity
     * @return
     */
    public Integer updManage(ErpContactEntity entity){
        return jdbcTemplate.update("update erp_contact SET name=?,number=?,cCategoryName=?,cCategory=?,contact=?,cLevelName=?,disable=?,user_id=?,province=?,city=?,county=?,address=? where id=? ",
                entity.getName(),entity.getNumber(),entity.getcCategoryName(),entity.getcCategory(),entity.getContact(),entity.getcLevelName(),entity.getDisable(),entity.getUserId()
                ,entity.getProvince(),entity.getCity(),entity.getCounty(),entity.getAddress(),entity.getId());
    }

    /**
     * 删除客户
     * @param id
     * @return
     */
    @Transactional
   public Integer delManage(Integer id){
       return jdbcTemplate.update("update erp_contact set isDelete=1 where id=?", id);
   }

    /**
     * 查询地址详细
     * @param contactId
     * @return
     */
   public ErpContactAddressVo getErpContactAddress(Integer contactId){
       StringBuffer sb=new StringBuffer().append("SELECT id,name,contact,province,city,county,address FROM erp_contact WHERE id=? ");
       ErpContactAddressVo addressVo = jdbcTemplate.queryForObject(sb.toString(), new BeanPropertyRowMapper<>(ErpContactAddressVo.class), contactId);
       addressVo.setAddressList(jdbcTemplate.query("SELECT SQL_CALC_FOUND_ROWS * FROM erp_contact_address WHERE contact_id=?",new BeanPropertyRowMapper<>(ErpContactAddressEntity.class),addressVo.getId()));
       return addressVo;
   }

    /**
     * 根据Id查询子地址详情
     * @param contactId
     * @return
     */
   public ErpContactAddressEntity getAddressIn(Integer contactId){
       return jdbcTemplate.queryForObject("select * from  erp_contact_address where id=?",new BeanPropertyRowMapper<>(ErpContactAddressEntity.class),contactId);
   }

    /**
     * 修改主表地址
     * @param userId
     * @param consignee
     * @param mobile
     * @param areaNameArray
     * @param address
     * @return
     */
   public Integer updAddress(int userId, String consignee, String mobile, String[] areaNameArray,String address){
       String provinceName = "";
       if (areaNameArray.length > 0) provinceName = areaNameArray[0];
       String cityName = "";
       if (areaNameArray.length > 1) cityName = areaNameArray[1];
       String districtName = "";
       if (areaNameArray.length > 2) districtName = areaNameArray[2];
       StringBuffer sb=new StringBuffer().append("update erp_contact set name=?,contact=?,province=?,city=?,county=?,address=? where id=?");
       return jdbcTemplate.update(sb.toString(),consignee,mobile,provinceName,cityName,districtName,address,userId);
   }

    /**
     * 新增子地址
     * @param userId
     * @param consignee
     * @param mobile
     * @param areaNameArray
     * @param areaCodeArray
     * @param address
     * @return
     */
   public Integer addAddressIn(Integer userId, String consignee, String mobile, String[] areaNameArray, String[] areaCodeArray, String address){
       String province = "";
       if (areaCodeArray.length > 0) province = areaCodeArray[0];
       String provinceName = "";
       if (areaNameArray.length > 0) provinceName = areaNameArray[0];
       String city = "";
       if (areaCodeArray.length > 1) city = areaCodeArray[1];
       String cityName = "";
       if (areaNameArray.length > 1) cityName = areaNameArray[1];
       String district = "";
       if (areaCodeArray.length > 2) district = areaCodeArray[2];
       String districtName = "";
       if (areaNameArray.length > 2) districtName = areaNameArray[2];
       StringBuffer sb = new StringBuffer().append("INSERT INTO erp_contact_address SET contact_id=?,consignee=?,mobile=?,province=?,province_name=?,city=?,city_name=?,district=?,district_name=?,address=?,create_on=? ");
       return jdbcTemplate.update(sb.toString(),userId,consignee,mobile,province,provinceName,city,cityName,district,districtName,address,System.currentTimeMillis()/1000);
   }

    /**
     * 修改子地址
     * @param addressId
     * @param consignee
     * @param mobile
     * @param areaNameArray
     * @param areaCodeArray
     * @param address
     * @return
     */
    public Integer updAddressIn(Integer addressId, String consignee, String mobile, String[] areaNameArray, String[] areaCodeArray, String address){
        String province = "";
        if (areaCodeArray.length > 0) province = areaCodeArray[0];
        String provinceName = "";
        if (areaNameArray.length > 0) provinceName = areaNameArray[0];
        String city = "";
        if (areaCodeArray.length > 1) city = areaCodeArray[1];
        String cityName = "";
        if (areaNameArray.length > 1) cityName = areaNameArray[1];
        String district = "";
        if (areaCodeArray.length > 2) district = areaCodeArray[2];
        String districtName = "";
        if (areaNameArray.length > 2) districtName = areaNameArray[2];
        StringBuffer sb = new StringBuffer().append("UPDATE erp_contact_address SET consignee=?,mobile=?,province=?,province_name=?,city=?,city_name=?,district=?,district_name=?,address=?,modify_on=? where id=? ");
        return jdbcTemplate.update(sb.toString(),consignee,mobile,province,provinceName,city,cityName,district,districtName,address,System.currentTimeMillis()/1000,addressId);
    }

    /**
     * 删除子地址
     * @param addressId
     * @return
     */
    public Integer delAddressIn(Integer addressId){
        return jdbcTemplate.update("delete from erp_contact_address where id=?",addressId);
    }

    public void addContact(Integer categoryId, String name, String place, String contact, String address, String remark) {
        String categoryName = "";
        Integer type = null;
        if(categoryId == 77){
            categoryName = "供应商";
            type = 10;
        }else if(categoryId == 1){
            categoryName = "消费者";
            type = -10;
        }else if(categoryId == 66){
            categoryName = "达人";
            type = 8;
        }else if(categoryId == 68){
            categoryName = "团长";
            type = 8;
        }
        String sql = "INSERT INTO erp_contact (name,cCategory,cCategoryName,place,contact,type,address,remark) VALUE (?,?,?,?,?,?,?,?)";

        jdbcTemplate.update(sql,name,categoryId,categoryName,place,contact,type,address,remark);
    }
}
